﻿using Infrastructure.Logging;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;

namespace Infrastructure.DbUtility
{
    public class DbHelper
    {
        private static ConnectionStringSettings connectionSetting = ConfigurationManager.ConnectionStrings["sqlConnectionString"];
        private static System.Data.Common.DbProviderFactory factory = null;
        private ILogger logger = null;
        private bool isTransaction = false;
        private DbConnection connection = null;
        private DbTransaction transaction = null;
        private QueryOptions queryOptions = null;
        public QueryOptions Options
        {
            get { return queryOptions; }
        }

        public DbHelper()
        {
            factory = DbProviderFactories.GetFactory(connectionSetting.ProviderName);
            queryOptions = new QueryOptions(connectionSetting.ProviderName);
        }

        public DbHelper(object o)
            : this()
        {
            logger = o as ILogger;
        }

        public DbHelper(string providerName, string connectionString)
        {
            connectionSetting = new ConnectionStringSettings("sqlConnectionString", connectionString, providerName);
            factory = DbProviderFactories.GetFactory(connectionSetting.ProviderName);
            queryOptions = new QueryOptions(connectionSetting.ProviderName);
        }

        public object Logger
        {
            get { return logger; }
            set { logger = value as ILogger; }
        }

        private void TraceStatement(DbCommand command)
        {
            if (logger == null) return;
            Microsoft.Practices.EnterpriseLibrary.Data.Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("sqlConnectionString");
            if (command.CommandType == CommandType.Text) logger.Write(Level.Debug, db.ConvertCommandText2Sql(command));
            else logger.Write(Level.Debug, db.ConvertStoredProcedure2Sql(command));
        }

        private DbConnection CreateConnection()
        {
            if (isTransaction && connection != null)
            {
                return connection;
            }
            else
            {
                connection = factory.CreateConnection();
                connection.ConnectionString = connectionSetting.ConnectionString;
            }
            if (connection.State != ConnectionState.Open) connection.Open();
            if (isTransaction && transaction == null) transaction = connection.BeginTransaction();
            return connection;
        }
        /// <summary>
        /// 执行多表查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DataSet ExecuteQuery(string cmdText, params DbParameter[] dbParams)
        {
            return ExecuteQuery(cmdText, CommandType.Text, dbParams);
        }
        /// <summary>
        /// 执行多表查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DataSet ExecuteQuery(string cmdText, CommandType cmdType, params DbParameter[] dbParams)
        {
            return ExecuteQuery(false, cmdText, cmdType, dbParams);
        }
        /// <summary>
        /// 执行多表查询
        /// </summary>
        /// <param name="includeSchema">是否包含架构信息</param>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DataSet ExecuteQuery(bool includeSchema, string cmdText, CommandType cmdType, params DbParameter[] dbParams)
        {
            DbConnection connection = CreateConnection();
            DataSet dataSet = new DataSet();
            DbCommand command = null;
            PrepareCommand(connection, ref command, cmdText, cmdType, dbParams);
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = command;
            TraceStatement(command);
            if (includeSchema) adapter.FillSchema(dataSet, SchemaType.Mapped);
            adapter.Fill(dataSet);
            return dataSet;
        }
        /// <summary>
        /// 执行多表查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DataSet ExecuteQueryEx(string formatCmd, params object[] args)
        {
            return ExecuteQueryEx(formatCmd, CommandType.Text, args);
        }
        /// <summary>
        /// 执行多表查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DataSet ExecuteQueryEx(string formatCmd, CommandType cmdType, params object[] args)
        {
            return ExecuteQueryEx(false, formatCmd, cmdType, args);
        }
        /// <summary>
        /// 执行多表查询
        /// </summary>
        /// <param name="includeSchema">是否包含架构信息</param>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DataSet ExecuteQueryEx(bool includeSchema, string formatCmd, CommandType cmdType, params object[] args)
        {
            DbParameter[] dbParams = BuildParameters(ref formatCmd, args);
            return ExecuteQuery(includeSchema, formatCmd, cmdType, dbParams);
        }
        /// <summary>
        /// 执行单表查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DataTable ExecQuery(string cmdText, params DbParameter[] dbParams)
        {
            return ExecQuery(cmdText, CommandType.Text, dbParams);
        }
        /// <summary>
        /// 执行单表查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DataTable ExecQuery(string cmdText, CommandType cmdType, params DbParameter[] dbParams)
        {
            return ExecQuery(false, cmdText, cmdType, dbParams);
        }
        /// <summary>
        /// 执行单表查询
        /// </summary>
        /// <param name="includeSchema">是否包含架构信息</param>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DataTable ExecQuery(bool includeSchema, string cmdText, CommandType cmdType, params DbParameter[] dbParams)
        {
            DataSet set = ExecuteQuery(includeSchema, cmdText, cmdType, dbParams);
            if (set != null && set.Tables.Count > 0) return set.Tables[0];
            else return null;
        }
        /// <summary>
        /// 执行单表查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DataTable ExecQueryEx(string formatCmd, params object[] args)
        {
            return ExecQueryEx(formatCmd, CommandType.Text, args);
        }
        /// <summary>
        /// 执行单表查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DataTable ExecQueryEx(string formatCmd, CommandType cmdType, params object[] args)
        {
            return ExecQueryEx(false, formatCmd, cmdType, args);
        }
        /// <summary>
        /// 执行单表查询
        /// </summary>
        /// <param name="includeSchema">是否包含架构信息</param>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DataTable ExecQueryEx(bool includeSchema, string formatCmd, CommandType cmdType, params object[] args)
        {
            DbParameter[] dbParams = BuildParameters(ref formatCmd, args);
            return ExecQuery(includeSchema, formatCmd, cmdType, dbParams);
        }
        /// <summary>
        /// 执行更新
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public int ExecuteUpdate(string cmdText, params DbParameter[] dbParams)
        {
            return ExecuteUpdate(cmdText, CommandType.Text, dbParams);
        }
        /// <summary>
        /// 执行更新
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public int ExecuteUpdate(string cmdText, CommandType cmdType, params DbParameter[] dbParams)
        {
            DbConnection connection = CreateConnection();
            DataSet dataSet = new DataSet();
            DbCommand command = null;
            PrepareCommand(connection, ref command, cmdText, cmdType, dbParams);
            TraceStatement(command);
            int result = command.ExecuteNonQuery();
            if (!isTransaction && connection.State != ConnectionState.Closed) connection.Close();
            return result;
        }
        /// <summary>
        /// 执行更新
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public int ExecuteUpdateEx(string formatCmd, params object[] args)
        {
            return ExecuteUpdateEx(formatCmd, CommandType.Text, args);
        }
        /// <summary>
        /// 执行更新
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public int ExecuteUpdateEx(string formatCmd, CommandType cmdType, params object[] args)
        {
            DbParameter[] dbParams = BuildParameters(ref formatCmd, args);
            return ExecuteUpdate(formatCmd, cmdType, dbParams);
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string cmdText, params DbParameter[] dbParams)
        {
            return ExecuteReader(cmdText, CommandType.Text, dbParams);
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string cmdText, CommandType cmdType, params DbParameter[] dbParams)
        {
            DbConnection connection = CreateConnection();
            DataSet dataSet = new DataSet();
            DbCommand command = null;
            PrepareCommand(connection, ref command, cmdText, cmdType, dbParams);
            TraceStatement(command);
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteReaderEx(string formatCmd, params object[] args)
        {
            return ExecuteReaderEx(formatCmd, CommandType.Text, args);
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteReaderEx(string formatCmd, CommandType cmdType, params object[] args)
        {
            DbParameter[] dbParams = BuildParameters(ref formatCmd, args);
            return ExecuteReader(formatCmd, cmdType, dbParams);
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, params DbParameter[] dbParams)
        {
            return ExecuteScalar(cmdText, CommandType.Text, dbParams);
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="cmdText">查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="dbParams">参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, CommandType cmdType, params DbParameter[] dbParams)
        {
            DbConnection connection = CreateConnection();
            DataSet dataSet = new DataSet();
            DbCommand command = null;
            PrepareCommand(connection, ref command, cmdText, cmdType, dbParams);
            TraceStatement(command);
            object result = command.ExecuteScalar();
            if (!isTransaction && connection.State != ConnectionState.Closed) connection.Close();
            return result;
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public object ExecuteScalarEx(string formatCmd, params object[] args)
        {
            return ExecuteScalarEx(formatCmd, CommandType.Text, args);
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="formatCmd">格式化查询T-SQL</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public object ExecuteScalarEx(string formatCmd, CommandType cmdType, params object[] args)
        {
            DbParameter[] dbParams = BuildParameters(ref formatCmd, args);
            return ExecuteScalar(formatCmd, cmdType, dbParams);
        }
        /// <summary>
        /// 准备命令参数 
        /// </summary>
        /// <param name="cmd">IDbCommand 命令对象</param>
        /// <param name="con">数据库连接对象</param>
        /// <param name="comType">解释命令字符串</param>
        /// <param name="comText">命令文本</param>
        /// <param name="parameter">数据操作命令参数化</param>
        /// <returns></returns>
        private void PrepareCommand(DbConnection connection, ref DbCommand command, string cmdText, CommandType cmdType, DbParameter[] dbParams)
        {
            if (command == null) command = connection.CreateCommand();
            if (isTransaction && transaction != null) command.Transaction = transaction;
            if (queryOptions.OptionChanged)
            {
                command.CommandType = CommandType.Text;
                command.CommandText = queryOptions.ToStatements();
                command.ExecuteNonQuery();
                queryOptions.CommitChanges();
            }
            command.CommandType = cmdType;
            command.CommandText = cmdText;
            if (dbParams != null && dbParams.Length > 0) command.Parameters.AddRange(dbParams);
        }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        protected DbParameter CreateParameter(string name, object value)
        {
            DbParameter parameter = factory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            return parameter;
        }
        /// <summary>
        /// 根据格式化字符传，生成sql参数。
        /// </summary>
        /// <param name="formatCmd">格式化字符串</param>
        /// <param name="parameters">sql参数的值</param>
        /// <returns>参数数组</returns>
        private DbParameter[] BuildParameters(ref string formatCmd, object[] parameters)
        {
            Regex regex = new Regex(@"{\d+}");
            List<DbParameter> lstParams = new List<DbParameter>();
            formatCmd = regex.Replace(formatCmd, delegate(Match match) {
                int index = int.Parse(match.Value.TrimStart('{').TrimEnd('}'));
                string p = "@p" + index;
                if (lstParams.Exists(a => a.ParameterName == p)) return p;
                object value = parameters[index];
                if (value == null) value = DBNull.Value;
                lstParams.Add(CreateParameter(p, value));
                return p;
            });
            return lstParams.ToArray();
        }

        public void BeginTransaction()
        {
            isTransaction = true;
        }

        public void RollbackTransaction()
        {
            var tranObj = transaction;
            isTransaction = false;
            transaction = null;
            tranObj.Rollback();
            if (connection.State != ConnectionState.Closed) connection.Close();
        }

        public void CommitTransation()
        {
            var tranObj = transaction;
            isTransaction = false;
            transaction = null;
            tranObj.Commit();
            if (connection.State != ConnectionState.Closed) connection.Close();
        }
    }
}
